package pannel.recipe;

import utils.JDBCUtils;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.time.LocalDateTime;

public class CommentDao {
    private static final String TABLE_NAME = "comment";

    // 分页查询评论
    public List<Comment> pageQuery(String searchText, int page, int pageSize) throws SQLException {
        int offset = (page - 1) * pageSize;
        String sql = "SELECT c.*, r.name as recipe_name, u.username " +
                    "FROM " + TABLE_NAME + " c " +
                    "LEFT JOIN recipe r ON c.recipe_id = r.id " +
                    "LEFT JOIN user u ON c.user_id = u.id " +
                    "WHERE c.content LIKE ? " +
                    "ORDER BY c.create_time DESC LIMIT ?, ?";
        
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, 
                "%" + searchText + "%",
                offset,
                pageSize);
        
        List<Comment> comments = new ArrayList<>();
        while (rs != null && rs.next()) {
            comments.add(convertToComment(rs));
        }
        return comments;
    }

    // 获取总记录数
    public int getTotalCount(String searchText) throws SQLException {
        String sql = "SELECT COUNT(*) FROM " + TABLE_NAME + " WHERE content LIKE ?";
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, "%" + searchText + "%");
        if (rs != null && rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    // 删除评论
    public boolean deleteById(int id) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
        int affected = (int) JDBCUtils.execute(sql, id);
        return affected > 0;
    }

    // 添加评论
    public boolean insert(Comment comment) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + 
                    " (recipe_id, user_id, content) VALUES (?, ?, ?)";
        int affected = (int) JDBCUtils.execute(sql,
                comment.getRecipeId(),
                comment.getUserId(),
                comment.getContent()
        );
        return affected > 0;
    }

    // 根据菜谱ID获取评论列表
    public List<Comment> getByRecipeId(int recipeId) throws SQLException {
        String sql = "SELECT c.*, r.name as recipe_name, u.username " +
                    "FROM " + TABLE_NAME + " c " +
                    "LEFT JOIN recipe r ON c.recipe_id = r.id " +
                    "LEFT JOIN user u ON c.user_id = u.id " +
                    "WHERE c.recipe_id = ? " +
                    "ORDER BY c.create_time DESC";
        
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, recipeId);
        List<Comment> comments = new ArrayList<>();
        while (rs != null && rs.next()) {
            comments.add(convertToComment(rs));
        }
        return comments;
    }

    private Comment convertToComment(ResultSet rs) throws SQLException {
        return Comment.builder()
                .id(rs.getInt("id"))
                .recipeId(rs.getInt("recipe_id"))
                .userId(rs.getInt("user_id"))
                .content(rs.getString("content"))
                .createTime(rs.getTimestamp("create_time").toLocalDateTime())
                .recipeName(rs.getString("recipe_name"))
                .username(rs.getString("username"))
                .build();
    }
} 